/*******************************************************************************
Rent_and_Population_Data.do

This file calculates population density and rents per square foot in ZIP codes and 
counties in the US. Data on population comes from the 2012-2016 5-year ACS. Data 
on rents per square foot comes from Zillow. We use Rental Values, Median ZRI Per
Sq Ft: SFR, Condo/Co-Op (downloaded November 2018), and look at rental values 
for October 2018.

This code first prepares things at the ZIP code level, then at the county level.

Last updated: 6/3/2021
*******************************************************************************/

version 15
cd "C:\Plants_in_Space"
set more off
set type double
set varabbrev off

************************PREPARING ZIP CODE-LEVEL DATA***************************
// This section prepares all the relevant data at the ZIP code level, by loading 
// raw Excel files, converting them to Stata format, and combining them together.

// Load in and save Zillow data
import delimited "Data\Raw\Zillow\Zip_ZriPerSqft_AllHomes.csv", clear 
keep regionname v103
ren regionname zip_code
ren v103 rent_per_sq_ft
label variable rent_per_sq_ft "Rent per square foot in Oct. 2018"
save "Data\Intermediate\Zillow\zip_code_rents_per_sq_foot.dta", replace

// Import and save data on ZIP code land area downloaded for 2014 from:
// https://www.census.gov/geographies/reference-files/time-series/geo/gazetteer-files.2014.html
import delimited "Data\Raw\Gazetteer\2014_Gaz_zcta_national.txt", clear
ren geoid zip_code
ren aland_sqmi zip_land_area
keep zip_code zip_land_area 
save "Data\Intermediate\Gazetteer\zip_code_land_area.dta", replace

// Import and save 2012-2016 5-year ACS population data. Data downloaded from
// NHGIS, series "Total Population" from dataset "2012_2016_ACS5a".
import delimited "Data\Raw\NHGIS\nhgis0018_ds225_20165_2016_zcta.csv", clear
ren af2le001 zip_code_pop
ren zcta5a zip_code
keep zip_code zip_code_pop
save "Data\Intermediate\NHGIS\zip_code_population.dta", replace

// Merge all datasets together
use "Data\Intermediate\Zillow\zip_code_rents_per_sq_foot.dta", clear
merge 1:1 zip_code using "Data\Intermediate\Gazetteer\zip_code_land_area.dta", nogen
merge 1:1 zip_code using "Data\Intermediate\NHGIS\zip_code_population.dta", nogen

// Calculate population density
gen pop_density = zip_code_pop / zip_land_area

save "Data\Final\FigureB1\zip_code_rents_and_population_density.dta", replace

**************************PREPARING COUNTY-LEVEL DATA***************************
// This section prepares all the relevant data at the county level, by loading 
// raw Excel files, converting them to Stata format, and combining them together.

// Load in Zillow data and create variable for county FIPS code
import delimited "Data\Raw\Zillow\County_ZriPerSqft_AllHomes.csv", clear 
gen county=statecodefips*1000+municipalcodefips
keep county v103
ren v103 rent_per_sq_ft
label variable rent_per_sq_ft "Rent per square foot in Oct. 2018"
save "Data\Intermediate\Zillow\county_rents_per_sq_foot.dta", replace

// Import and save data on county land area downloaded for 2014 from:
// https://www.census.gov/geographies/reference-files/time-series/geo/gazetteer-files.2014.html
import delimited "Data\Raw\Gazetteer\2014_Gaz_counties_national.txt", clear
ren geoid county
ren aland_sqmi county_land_area
keep county county_land_area 
save "Data\Intermediate\Gazetteer\county_land_area.dta", replace

// Import and save 2012-2016 5-year ACS population data. Data downloaded from
// NHGIS, series "Total Population" from dataset "2012_2016_ACS5a".
import delimited "Data\Raw\NHGIS\nhgis0018_ds225_20165_2016_county.csv", clear
drop county
gen county=statea*1000+countya
ren af2le001 county_pop
keep county county_pop
order county county_pop
save "Data\Intermediate\NHGIS\county_population.dta", replace

// Merge all datasets together
use "Data\Intermediate\Zillow\county_rents_per_sq_foot.dta", clear
merge 1:1 county using "Data\Intermediate\Gazetteer\county_land_area.dta", nogen
merge 1:1 county using "Data\Intermediate\NHGIS\county_population.dta", nogen

// Calculate population density
gen pop_density = county_pop / county_land_area

save "Data\Final\FigureB1\county_rents_and_population_density.dta", replace
